/**
	Program name	: LoadData.java
 **/

package com.map.home;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.Date;
import java.util.Iterator;
import java.util.List;

import javax.swing.JOptionPane;

import com.map.domain.AsItem;
import com.map.domain.MapData;
import com.map.manager.ConfigManager;
import com.map.manager.DBConnectionManager;
import com.map.manager.SystemLogManager;
import com.map.util.Constants;
import com.map.util.LogManager;
import com.map.util.StringUtil;

public class LoadData {
	
	private LogManager logmanager;
	private SystemLogManager sysLogMgr;

	// Config Declaration
	private static LoadData instance = null;
	static final String CONFIG_FILE = "/properties/autoscript.properties";
	static final String TABLE_AS_NAME  = Constants.SCHEMA_ADMIN + "TBL_AUTOSCRIPT";
	static final java.util.Properties appCfg = new java.util.Properties();

	private LoadData() {
		// Load Properties Value
		try {
			InputStream stream = this.getClass().getResourceAsStream(
					CONFIG_FILE);
			if (stream != null)
				appCfg.load(stream);
			else
				throw new Exception("Input Stream is NULL");
		} catch (Exception e) {
			e.printStackTrace();
		}

		// Initiate LogManager
		logmanager = LogManager.getInstance(ConfigManager.getInstance().get("AppLogFile"));
		logmanager.logMsg("LoadData Init");
		
		// Initiatet SystemLog
		sysLogMgr = SystemLogManager.getInstance();
	}
	
	public static LoadData getInstance() {
		//thread safe implementation - double-check idiom
		if (instance == null) {
			synchronized (LoadData.class) {
				if (instance == null) {
					instance = new LoadData();
				}
			}
		}
		return instance;
	}

	// To get list of applications
	public List<AsItem> listApplications() {
		logmanager.logMsg("Start: listApplications");
		int TIMEOUT = 10000; 		//DB connection timeout if unable to get connection in less than 10 seconds
		String connectionPool 		= ConfigManager.getInstance().getConnectionPool();
		DBConnectionManager connMgr = DBConnectionManager.getInstance();
		String timeOutValue 		= ConfigManager.getInstance().get(Constants.DB_WAIT_TIME);

		if (timeOutValue != null) {
			try { TIMEOUT = Integer.parseInt(timeOutValue); } catch (NumberFormatException e) {}
		}

		List<AsItem> list = new ArrayList<AsItem>();
		Connection connection = null;
		PreparedStatement selectPstmt = null;
		try {
			connection = connMgr.getConnection(connectionPool, TIMEOUT);
			if (connection == null) {
				logmanager.logMsg("listApplications :> Unable To Get Database Connection.");
			} else {
			
				String SqlStmt = " SELECT DISTINCT APPLICATION_ID " +
								 " FROM " + TABLE_AS_NAME + " ORDER BY APPLICATION_ID ";
	
				logmanager.logMsg(SqlStmt);
				selectPstmt = connection.prepareStatement(SqlStmt);
				ResultSet rs = selectPstmt.executeQuery();
	
				while (rs.next()) {
					String value 		= rs.getString(1);
					list.add(new AsItem(value, value));
				}
		
				rs.close();
				selectPstmt.close();
				//connection.close();
			}
		} catch (Exception ex) {
			ex.printStackTrace();
			sysLogMgr.logError(ex);
			JOptionPane.showMessageDialog(null, "Got problem!!! \n\nDetail: "+ex.getMessage(), "Error", JOptionPane.ERROR_MESSAGE);
		} finally {
			if (selectPstmt != null) {
				try { selectPstmt.close(); 
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			connMgr.freeConnection(connectionPool, connection);
		}
		logmanager.logMsg("End: listApplications");
		return (list == null || list.isEmpty())? null: list;
	}
	
	// To get list of products by application no
	public List<AsItem> listProductsByApplication(String applicationNo) {
		logmanager.logMsg("Start: listProductsByApplication");
		int TIMEOUT = 10000; 		//DB connection timeout if unable to get connection in less than 10 seconds
		String connectionPool 		= ConfigManager.getInstance().getConnectionPool();
		DBConnectionManager connMgr = DBConnectionManager.getInstance();
		String timeOutValue 		= ConfigManager.getInstance().get(Constants.DB_WAIT_TIME);

		if (timeOutValue != null) {
			try { TIMEOUT = Integer.parseInt(timeOutValue); } catch (NumberFormatException e) {}
		}

		List<AsItem> list = new ArrayList<AsItem>();
		Connection connection = null;
		PreparedStatement selectPstmt = null;
		try {
			connection = connMgr.getConnection(connectionPool, TIMEOUT);
			if (connection == null) {
				logmanager.logMsg("listProductsByApplication :> Unable To Get Database Connection.");
			} else {
			
				String SqlStmt = " SELECT DISTINCT PRODUCT_ID FROM " + TABLE_AS_NAME + " WHERE APPLICATION_ID = ? ORDER BY PRODUCT_ID ";
	
				logmanager.logMsg(SqlStmt);
				selectPstmt = connection.prepareStatement(SqlStmt);
				selectPstmt.setString(1, applicationNo);
				ResultSet rs = selectPstmt.executeQuery();
	
				while (rs.next()) {
					String value 		= rs.getString(1);
					list.add(new AsItem(value, value));
				}
		
				rs.close();
				selectPstmt.close();
				//connection.close();
			}
		} catch (Exception ex) {
			ex.printStackTrace();
			sysLogMgr.logError(ex);
			JOptionPane.showMessageDialog(null, "Got problem!!! \n\nDetail: "+ex.getMessage(), "Error", JOptionPane.ERROR_MESSAGE);
		} finally {
			if (selectPstmt != null) {
				try { selectPstmt.close(); 
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			connMgr.freeConnection(connectionPool, connection);
		}
		logmanager.logMsg("End: listProductsByApplication");
		return (list == null || list.isEmpty())? null: list;
	}
	
	// To get list of screens by application no and product no
	public List<AsItem> listScreensByProductAndApplication(String applicationNo, String productNo) {
		logmanager.logMsg("Start: listScreensByProductAndApplication");
		int TIMEOUT = 10000; 		//DB connection timeout if unable to get connection in less than 10 seconds
		String connectionPool 		= ConfigManager.getInstance().getConnectionPool();
		DBConnectionManager connMgr = DBConnectionManager.getInstance();
		String timeOutValue 		= ConfigManager.getInstance().get(Constants.DB_WAIT_TIME);

		if (timeOutValue != null) {
			try { TIMEOUT = Integer.parseInt(timeOutValue); } catch (NumberFormatException e) {}
		}

		List<AsItem> list = new ArrayList<AsItem>();
		Connection connection = null;
		PreparedStatement selectPstmt = null;
		try {
			connection = connMgr.getConnection(connectionPool, TIMEOUT);
			if (connection == null) {
				logmanager.logMsg("listScreensByProductAndApplication :> Unable To Get Database Connection.");
			} else {
			
				String SqlStmt = " SELECT DISTINCT SCREEN_ID " +
								 " FROM " + TABLE_AS_NAME + 
								 " WHERE APPLICATION_ID = ? " +
								 "	 AND PRODUCT_ID     = ? " + 
								 " ORDER BY SCREEN_ID ";
	
				logmanager.logMsg(SqlStmt);
				selectPstmt = connection.prepareStatement(SqlStmt);
				selectPstmt.setString(1, applicationNo);
				selectPstmt.setString(2, productNo);
				ResultSet rs = selectPstmt.executeQuery();
	
				while (rs.next()) {
					String value 		= rs.getString(1);
					list.add(new AsItem(value, value));
				}
		
				rs.close();
				selectPstmt.close();
				
				// reorder screen list
				if (list != null && !list.isEmpty()) {
					
					String screenOrderList = appCfg.getProperty("screen.order.list");
					if (!StringUtil.isEmpty(screenOrderList)) {
						String[] scrArr = screenOrderList.split(Constants.COMMA, -1);
						Iterator<AsItem> itr = list.iterator();
						AsItem item = null;
						while (itr.hasNext()) {
							item = itr.next();
							
							for (int i = 0; i < scrArr.length; i++) {
								if (!StringUtil.isEmpty(scrArr[i]) && scrArr[i].trim().equals(item.getValue())) {
									item.setIndex(i);
									break;
								}
							}
						}

						Collections.sort(list,new Comparator<AsItem>() {
		                     public int compare(AsItem s1,AsItem s2) {
		             	    	int res = 0;
		            	    	if (s1.getIndex() > s2.getIndex()) 
		            	    		res = 1;
		            	    	if (s1.getIndex() < s2.getIndex())
		            	    		res = -1;
		            	    	return res;
		                     }});
						
					}
				}
			}
		} catch (Exception ex) {
			ex.printStackTrace();
			sysLogMgr.logError(ex);
			JOptionPane.showMessageDialog(null, "Got problem!!! \n\nDetail: "+ex.getMessage(), "Error", JOptionPane.ERROR_MESSAGE);
		} finally {
			if (selectPstmt != null) {
				try { selectPstmt.close(); 
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			connMgr.freeConnection(connectionPool, connection);
		}
		logmanager.logMsg("End: listScreensByProductAndApplication");
		return (list == null || list.isEmpty())? null: list;
	}
	

	// To get detail data by application no, product no and screen no
	public MapData getDetailData(String applicationNo, String productNo, String screenNo) {
		logmanager.logMsg("Start: getDetailData");
		int TIMEOUT = 10000; 		//DB connection timeout if unable to get connection in less than 10 seconds
		String connectionPool 		= ConfigManager.getInstance().getConnectionPool();
		DBConnectionManager connMgr = DBConnectionManager.getInstance();
		String timeOutValue 		= ConfigManager.getInstance().get(Constants.DB_WAIT_TIME);

		if (timeOutValue != null) {
			try { TIMEOUT = Integer.parseInt(timeOutValue); } catch (NumberFormatException e) {}
		}

		MapData mapData = null;
		Connection connection = null;
		PreparedStatement selectPstmt = null;
		try {
			connection = connMgr.getConnection(connectionPool, TIMEOUT);
			if (connection == null) {
				logmanager.logMsg("getDetailData :> Unable To Get Database Connection.");
			} else {
			
				String SqlStmt = " SELECT APPLICATION_ID, PRODUCT_ID, SCREEN_ID, CONTENT_TEXT, CREATED_BY, CREATED_DATE, UPDATED_BY, UPDATED_DATE " +
								 " FROM " + TABLE_AS_NAME + 
								 " WHERE APPLICATION_ID = ? " +
								 "	 AND PRODUCT_ID     = ? " +
								 "	 AND SCREEN_ID      = ? ";
	
				logmanager.logMsg(SqlStmt);
				selectPstmt = connection.prepareStatement(SqlStmt);
				selectPstmt.setString(1, applicationNo);
				selectPstmt.setString(2, productNo);
				selectPstmt.setString(3, screenNo);
				ResultSet rs = selectPstmt.executeQuery();
	
				if (rs.next()) {
					String appNo 		= rs.getString(1);
					String prodNo 		= rs.getString(2);
					String scrNo		= rs.getString(3);
					String contentText	= rs.getString(4);
					String createdBy 	= rs.getString(5);
					Date   createdDate 	= rs.getDate(6);
					String updatedBy 	= rs.getString(7);
					Date   updatedDate 	= rs.getDate(8);
					
					mapData = new MapData(appNo, prodNo, scrNo, contentText, createdBy, createdDate, updatedBy, updatedDate);
				}
		
				rs.close();
				selectPstmt.close();
				//connection.close();
			}
		} catch (Exception ex) {
			ex.printStackTrace();
			sysLogMgr.logError(ex);
			JOptionPane.showMessageDialog(null, "Got problem!!! \n\nDetail: "+ex.getMessage(), "Error", JOptionPane.ERROR_MESSAGE);
		} finally {
			if (selectPstmt != null) {
				try { selectPstmt.close(); 
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			connMgr.freeConnection(connectionPool, connection);
		}
		logmanager.logMsg("End: getDetailData");
		return mapData;
	}

	/**
	 * Build script to send/keyin to PCAM
	 */
	public String[] buildScriptToSend(String appNo, String prodNo, String screenNo, String[] fieldDatas) {
		String[] result = new String[2]; // 0: script format;  1: vbscript to send data to PCAM
		StringBuffer script = new StringBuffer();
		logmanager.logMsg("## Start build message for: " + appNo + " : " + prodNo + " : " + screenNo);
		sysLogMgr.logMsg("## Start build message for: " + appNo + " : " + prodNo + " : " + screenNo);
		String TAB = "{TAB}";
		
		try {

			// Get script from ScreenNo_ProductNo.txt or from System Properties
			String scriptFileName = screenNo + "_" + prodNo + ".txt";
			String fnScript = getScriptFromFile(scriptFileName);
			
			// if does not exist script from text file, try to get from properties
			if (StringUtil.isEmpty(fnScript)) {
				logmanager.logMsg("script file " + scriptFileName + " does not exist, start reading script from system properties");
				String scrIndex = appCfg.getProperty(Constants.SCR_MAP.replaceFirst(Constants.TEMPCH, screenNo));
				fnScript = appCfg.getProperty(Constants.SCR_FNAME_SCRIPT.replaceFirst(Constants.TEMPCH, scrIndex));
			}
			
			String full 	= appCfg.getProperty(Constants.PCAM_KI_F_FULL);
			String notFull 	= appCfg.getProperty(Constants.PCAM_KI_F_NOTFULL);
			
			logmanager.logMsg("fnScript = " + fnScript);
			result[0] = fnScript;
			if (!StringUtil.isEmpty(fnScript)) {
				
				// process to apply \\{TODAY-?[\\d][dMy],[dMy]+\\}
				fnScript = StringUtil.replaceByTodayPattern(fnScript);
				
				String[] fieldFormats = fnScript.split(Constants.DDOT, -1);
				if (fieldFormats != null && fieldFormats.length > 0 && 
						fieldDatas != null && fieldDatas.length > 0) {

					int maxLen     = 0;
					int remainSpas = 0;
					String value   = "";
					boolean isFixed = false;

					// Start: for each format (data, fix)
					logmanager.logMsg("start loop fieldFormats: size = " + fieldFormats.length);
					StringBuffer oneField = null;
					for (int i = 0; i < fieldFormats.length; i++) {
						oneField = new StringBuffer();
						String format = fieldFormats[i];

						if (StringUtil.isEmpty(format)) {
							isFixed = true;
							value = TAB;			// press TAB
							remainSpas = 0;

						} else if (StringUtil.isFixed(format)) { // Fix/New Field
							isFixed = true;
							// format: [F-default-maxlength]
							String[] arr = format.split(Constants.SUBT,-1);  
							try{
								value 		 = arr[1];  // default value for FIX
							} catch (ArrayIndexOutOfBoundsException arrIdxOutOfEx) {
								JOptionPane.showMessageDialog(null, "The fixed format (index "+(i+1)+") in script is worng !!! \n\nDetail: "+arrIdxOutOfEx.getMessage(),"Error", JOptionPane.ERROR_MESSAGE);
								return result;
							}
							// Descypt
							value = StringUtil.descrypt(value);
							
							if (arr.length > 2) { //F-xxx-mmm
								maxLen   	 = Integer.parseInt(arr[2]); // max length for FIX
								remainSpas 	 = maxLen - value.length();
							} else { // F-xxx
								remainSpas   = 0;
							}

						} else {

							// format: [index-length]
							int index  	= Integer.parseInt(format.split(Constants.SUBT)[0].trim()); // index of data in Grid
							try{
								maxLen    	= Integer.parseInt(format.split(Constants.SUBT)[1]); // length of data
							} catch (ArrayIndexOutOfBoundsException arrIdxOutOfEx) {
								JOptionPane.showMessageDialog(null, "The format (index "+(i+1)+") in script is worng !!! \n\nDetail: "+arrIdxOutOfEx.getMessage(),"Error", JOptionPane.ERROR_MESSAGE);
								return result;
							}
							
							value 		= (index <= fieldDatas.length)? fieldDatas[index-1]:"";
							remainSpas = maxLen - value.length();

						}
					
						// Build VBScript
						if (remainSpas < 0) {
							// in case: data field is over space in PCAM, process cutting and return suitable len
							logmanager.logMsg("   the data item ["+(i+1)+"] (" + value + ") is longer than space in PCAM len= " + maxLen);
							logmanager.logMsg("continue processing with cutting data......");
							value = value.substring(0, maxLen);
						}

						// add field data (encode as vbscript)
						oneField.append((isFixed)? value: StringUtil.vbsEncode(value)); // Encode with data from database
																					  // The fixed data can include hot-key
						// add special char
						if (i < (fieldFormats.length - 1)) {
							// not last format
							oneField.append((remainSpas > 0)? notFull: full);
						} else {
							// last character
							oneField.append(appCfg.getProperty(Constants.PCAM_KI_F_ENDING));  // ending word
						}

						// reset temporary variables
						remainSpas = 0;
						isFixed = false;

						// append send script
						//script.append("WshShell.SendKeys \"" + oneField.toString() + "\"\n");
						script.append(StringUtil.replaceByWaitPattern(oneField.toString()));
						// append sleep in ms
						script.append("WScript.Sleep " + appCfg.getProperty(Constants.PCAM_FIELD_SLEEP) + "\n");

					} // End: for each format
					result[1] = script.toString(); 
				}
	 		}
			
		} catch (NumberFormatException numEx) {
			numEx.printStackTrace();
			sysLogMgr.logMsg("Exception for round: " + appNo + " : " + prodNo + " : " + screenNo);
			sysLogMgr.logError(numEx);
			JOptionPane.showMessageDialog(null, "The index in script is not number !!! \n\nDetail: "+numEx.getMessage(),"Error", JOptionPane.ERROR_MESSAGE);
		} catch (Exception ex) {
			ex.printStackTrace();
			sysLogMgr.logMsg("Exception for round: " + appNo + " : " + prodNo + " : " + screenNo);
			sysLogMgr.logError(ex);
			JOptionPane.showMessageDialog(null, "Got problem!!! \n\nDetail: "+ex.getMessage(), "Error", JOptionPane.ERROR_MESSAGE);
		}

		logmanager.logMsg("## End build message for: " + appNo + " : " + prodNo + " : " + screenNo);
		return result;
	}

	private String getScriptFromFile(String fileName) {
		String result = "";
		BufferedReader br = null;
		try {
			String appScriptPath = ConfigManager.getInstance().get("AppScriptPath");
			String filePath = appScriptPath + File.separator + fileName;
			File file = new File(filePath);
			
			if (file.exists()) {
				br = new BufferedReader(new FileReader(file.getAbsoluteFile()));
				result = br.readLine();
			}
			
		} catch (Exception e) {
			sysLogMgr.logError(e);
			JOptionPane.showMessageDialog(null, "Script file ["+ fileName +"] got problem" + "\n\nDetail: "+e.getMessage(), "Error", JOptionPane.ERROR_MESSAGE);
		} finally {
			if (br != null)
				try {
					br.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
		}
		return result;
	}
	
	/**
	 * Build data detail for datagrid
	 */
	public String[][] buildDataGrid(String screenNo, String[] fieldDatas) {
		String[][] datagrid = null;
		
		String scrIndex = appCfg.getProperty(Constants.SCR_MAP.replaceFirst(Constants.TEMPCH, screenNo));
		String fnames = appCfg.getProperty(Constants.SCR_FNAME_KEY.replaceFirst(Constants.TEMPCH, scrIndex));
		if (!StringUtil.isEmpty(fnames)) {
			String[] fieldNames = fnames.split(Constants.COMMA, -1);
			if (fieldNames != null && fieldNames.length > 0 && 
					fieldDatas != null && fieldDatas.length > 0) {
				int fnRows = fieldNames.length;
				int fdRows = fieldDatas.length;
				int rows = Math.max(fnRows, fdRows);
				datagrid = new String[rows][3];
				
				for (int i = 0; i < rows; i++) {
					datagrid[i][0] = (i<fnRows)? fieldNames[i]:"";
					datagrid[i][1] = (i<fdRows)? fieldDatas[i]:"";
					datagrid[i][2] = (i+1) + "";
				}
			}
 		}
		
		return datagrid;
	}
	
	
	// To get list of applications
	public String checkConnection() {
		String res = "";
		logmanager.logMsg("Start: checkConnection");
		int TIMEOUT = 10000; 		//DB connection timeout if unable to get connection in less than 10 seconds
		Connection connection 		= null;
		DBConnectionManager connMgr = null;
		String connectionPool 		= "";

		try {
			connectionPool 				= ConfigManager.getInstance().getConnectionPool();
			connMgr 					= DBConnectionManager.getInstance();
			String timeOutValue 		= ConfigManager.getInstance().get(Constants.DB_WAIT_TIME);

			if (timeOutValue != null) {
				try { TIMEOUT = Integer.parseInt(timeOutValue); } catch (NumberFormatException e) {}
			}

			connection = connMgr.getConnection(connectionPool, TIMEOUT);
			if (connection == null) {
				res = "Unable To Get Database Connection.";
			}
		} catch (Exception ex) {
			ex.printStackTrace();
			sysLogMgr.logError(ex);
			res = ex.getMessage();
		} finally {
			connMgr.freeConnection(connectionPool, connection);
		}
		logmanager.logMsg("End: checkConnection");
		
		if (!StringUtil.isEmpty(res)) {
			// error
			JOptionPane.showMessageDialog(null, "database connection error \n\nDetail: " + res, "Error", JOptionPane.ERROR_MESSAGE);
		}
		return res;
	}
	
	public static void main(String[] args) {
		LoadData imp = new LoadData();
		List<AsItem> appList = imp.listApplications();
		if (appList != null && !appList.isEmpty()) {
			for (AsItem asItem : appList) {
				System.out.println(asItem.getValue());
				List<AsItem> prodList = imp.listProductsByApplication(asItem.getValue());	
				if (prodList != null && !prodList.isEmpty()) {
					for (AsItem asItem2 : prodList) {
						System.out.println("	" + asItem2.getValue());
						List<AsItem> screenList = imp.listScreensByProductAndApplication(asItem.getValue(), asItem2.getValue());
						if (screenList != null && !screenList.isEmpty()) {
							for (AsItem asItem3 : screenList) {
								System.out.println("		" + asItem3.getValue());
								MapData mapData = imp.getDetailData(asItem.getValue(), asItem2.getValue(), asItem3.getValue());
								System.out.println("			" + mapData.getContentText());
							}
						}
					}
				}
			}
			
		}
	}
}
